﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Windows.Forms;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using System.IO;
using System.Data;
using SDD;
using NPOI.HSSF.UserModel;

namespace SDD
{
    class QueryData
    {
        public string url1 = "http://newjust.masterlink.com.tw/z/zg/zg_A_0_2.djhtm";
        public string url2 = "http://newjust.masterlink.com.tw/z/zg/zg_A_1_2.djhtm";
        public string url3 = "http://newjust.masterlink.com.tw/z/zg/zg_AA_0_2.djhtm";
        public string url4 = "http://newjust.masterlink.com.tw/z/zg/zg_AA_1_2.djhtm";

        private string FullFileName = Program.FileDir + "兩日漲跌幅.xls";
        private string destFileName = Program.FileDir + "兩日漲跌幅" + DateTime.Today.ToString("yyyy-MM-dd") + ".xls";

        public string dataDay = Program.DataDay;
        public string msg = "";
        public dds.ddtDataTable dt1 = new dds.ddtDataTable();
        public dds.ddtDataTable dt2 = new dds.ddtDataTable();
        public int totalSheetCnt = 0;
        public void AggregateData()
        {
            try
            {
                analysisHTML(url1, dt1);
                analysisHTML(url2, dt1);
                analysisHTML(url3, dt2);
                analysisHTML(url4, dt2);
                InsertData();
                //new UpdateData().UpdateDataToGoogleDoc(FullFileName);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.Message);
                Console.WriteLine("兩日漲跌幅 資料擷取失敗!!");
            }

        }

        private void InsertData()
        {

            dt1.DefaultView.Sort = "Volume desc";
            dt2.DefaultView.Sort = "Volume desc";
            string befName = "";

            NPOIComFunction xls = new NPOIComFunction(FullFileName);
            if (xls.Workbook.GetSheetIndex(dataDay) > -1)
            {
                Console.WriteLine("兩日漲跌幅 " + dataDay + " 資料已存在!!!");
                return;
            }
            HSSFSheet sheet = xls.Workbook.CreateSheet(dataDay);
            try
            {
                befName = xls.Workbook.GetSheetName(xls.Workbook.GetSheetIndex(dataDay) - 1);
            }
            catch
            { }

            // 開始寫資料
            sheet.CreateRow(0).CreateCell(0).SetCellValue("股票名稱");
            sheet.CreateRow(0).CreateCell(1).SetCellValue("收盤價");
            sheet.CreateRow(0).CreateCell(2).SetCellValue("漲跌");
            sheet.CreateRow(0).CreateCell(3).SetCellValue("漲跌幅");
            sheet.CreateRow(0).CreateCell(4).SetCellValue("成交量");
            sheet.CreateRow(0).CreateCell(5).SetCellValue("2日漲跌");
            sheet.CreateRow(0).CreateCell(6).SetCellValue("2日漲跌幅");
            sheet.CreateRow(0).CreateCell(7).SetCellValue("進榜");

            sheet.CreateRow(0).CreateCell(9).SetCellValue("股票名稱");
            sheet.CreateRow(0).CreateCell(10).SetCellValue("收盤價");
            sheet.CreateRow(0).CreateCell(11).SetCellValue("漲跌");
            sheet.CreateRow(0).CreateCell(12).SetCellValue("漲跌幅");
            sheet.CreateRow(0).CreateCell(13).SetCellValue("成交量");
            sheet.CreateRow(0).CreateCell(14).SetCellValue("2日漲跌");
            sheet.CreateRow(0).CreateCell(15).SetCellValue("2日漲跌幅");
            sheet.CreateRow(0).CreateCell(16).SetCellValue("進榜");

            int x = 0;

            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                x++;
                try
                {
                    sheet.CreateRow(x).CreateCell(0).SetCellValue(dt1.DefaultView[i]["ID"].ToString() + " " + dt1.DefaultView[i]["Name"].ToString());
                    sheet.CreateRow(x).CreateCell(1).SetCellValue(dt1.DefaultView[i]["ClosingPrice"].ToString());
                    sheet.CreateRow(x).CreateCell(2).SetCellValue(dt1.DefaultView[i]["Fluctuation"].ToString());
                    sheet.CreateRow(x).CreateCell(3).SetCellValue(dt1.DefaultView[i]["FluctuationPerCent"].ToString());
                    sheet.CreateRow(x).CreateCell(4).SetCellValue(dt1.DefaultView[i]["Volume"].ToString());
                    sheet.CreateRow(x).CreateCell(5).SetCellValue(dt1.DefaultView[i]["D2Fluctuation"].ToString());
                    sheet.CreateRow(x).CreateCell(6).SetCellValue(dt1.DefaultView[i]["D2FluctuationPerCent"].ToString());

                    string SIDName = dt1.DefaultView[i]["ID"].ToString() + " " + dt1.DefaultView[i]["Name"].ToString();
                    int inRecCnt = 0;
                    bool IsContinue = false;
                    //迴圈跑是否持續進榜
                    for (int befDayCnt = 1; i < xls.Workbook.NumberOfSheets; befDayCnt++)
                    {
                        // befDayCnt 要往前推算幾天
                        //1.先取得工作表名稱
                        try
                        {
                            HSSFSheet befSheet = xls.Workbook.GetSheetAt(xls.Workbook.GetSheetIndex(dataDay) - befDayCnt);
                            IsContinue = false;
                            //2.跑工作表迴圈
                            //befSheet.LastRowNum應該為2~101
                            for (int befSheetRowCnt = 1; befSheetRowCnt <= befSheet.LastRowNum; befSheetRowCnt++)
                            {
                                //漲幅的IDNAME 在第0列
                                if (befSheet.GetRow(befSheetRowCnt).GetCell(0).StringCellValue == SIDName)
                                {
                                    IsContinue = true;
                                    inRecCnt++;
                                    break; //離開befSheet
                                }
                            }
                            if (IsContinue == false)
                                break; //離開計算持續進榜
                        }
                        catch { }
                    }
                    if (inRecCnt == 0)
                    {
                        sheet.CreateRow(x).CreateCell(0).SetCellValue(dt1.DefaultView[i]["ID"].ToString() + " " + dt1.DefaultView[i]["Name"].ToString());
                        sheet.CreateRow(x).CreateCell(1).SetCellValue(dt1.DefaultView[i]["ClosingPrice"].ToString());
                        sheet.CreateRow(x).CreateCell(2).SetCellValue(dt1.DefaultView[i]["Fluctuation"].ToString());
                        sheet.CreateRow(x).CreateCell(3).SetCellValue(dt1.DefaultView[i]["FluctuationPerCent"].ToString());
                        sheet.CreateRow(x).CreateCell(4).SetCellValue(dt1.DefaultView[i]["Volume"].ToString());
                        sheet.CreateRow(x).CreateCell(5).SetCellValue(dt1.DefaultView[i]["D2Fluctuation"].ToString());
                        sheet.CreateRow(x).CreateCell(6).SetCellValue(dt1.DefaultView[i]["D2FluctuationPerCent"].ToString());
                        sheet.CreateRow(x).CreateCell(7).SetCellValue("新進榜");
                    }
                    else //寫入次數並上色
                    {
                        HSSFCellStyle style1 = xls.Workbook.CreateCellStyle();
                        if (inRecCnt == 1)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
                        else if (inRecCnt >= 2 && inRecCnt <= 5)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.TAN.index;
                        else if (inRecCnt > 5)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROSE.index;
                        style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;

                        HSSFCell cell0 = sheet.CreateRow(x).CreateCell(0);
                        cell0.CellStyle = style1;
                        cell0.SetCellValue(dt1.DefaultView[i]["ID"].ToString() + " " + dt1.DefaultView[i]["Name"].ToString());

                        HSSFCell cell1 = sheet.CreateRow(x).CreateCell(1);
                        cell1.CellStyle = style1;
                        cell1.SetCellValue(dt1.DefaultView[i]["ClosingPrice"].ToString());

                        HSSFCell cell2 = sheet.CreateRow(x).CreateCell(2);
                        cell2.CellStyle = style1;
                        cell2.SetCellValue(dt1.DefaultView[i]["Fluctuation"].ToString());

                        HSSFCell cell3 = sheet.CreateRow(x).CreateCell(3);
                        cell3.CellStyle = style1;
                        cell3.SetCellValue(dt1.DefaultView[i]["FluctuationPerCent"].ToString());

                        HSSFCell cell4 = sheet.CreateRow(x).CreateCell(4);
                        cell4.CellStyle = style1;
                        cell4.SetCellValue(dt1.DefaultView[i]["Volume"].ToString());

                        HSSFCell cell5 = sheet.CreateRow(x).CreateCell(5);
                        cell5.CellStyle = style1;
                        cell5.SetCellValue(dt1.DefaultView[i]["D2Fluctuation"].ToString());

                        HSSFCell cell6 = sheet.CreateRow(x).CreateCell(6);
                        cell6.CellStyle = style1;
                        cell6.SetCellValue(dt1.DefaultView[i]["D2FluctuationPerCent"].ToString());

                        HSSFCell cell7 = sheet.CreateRow(x).CreateCell(7);
                        cell7.CellStyle = style1;
                        cell7.SetCellValue("持續進榜[" + inRecCnt.ToString() + "]");
                    }

                    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                    sheet.CreateRow(x).CreateCell(9).SetCellValue(dt2.DefaultView[i]["ID"].ToString() + " " + dt2.DefaultView[i]["Name"].ToString());
                    sheet.CreateRow(x).CreateCell(10).SetCellValue(dt2.DefaultView[i]["ClosingPrice"].ToString());
                    sheet.CreateRow(x).CreateCell(11).SetCellValue(dt2.DefaultView[i]["Fluctuation"].ToString());
                    sheet.CreateRow(x).CreateCell(12).SetCellValue(dt2.DefaultView[i]["FluctuationPerCent"].ToString());
                    sheet.CreateRow(x).CreateCell(13).SetCellValue(dt2.DefaultView[i]["Volume"].ToString());
                    sheet.CreateRow(x).CreateCell(14).SetCellValue(dt2.DefaultView[i]["D2Fluctuation"].ToString());
                    sheet.CreateRow(x).CreateCell(15).SetCellValue(dt2.DefaultView[i]["D2FluctuationPerCent"].ToString());
                    //if (befName.Length > 0)
                    //    sheet.CreateRow(x).CreateCell(16).SetCellFormula(string.Format("IF(COUNTIF('{0}'!J2:J101,{1})>=1,\"持續進榜\",\"新進榜\")", befName, "J" + (x + 1).ToString()));

                    SIDName = dt2.DefaultView[i]["ID"].ToString() + " " + dt2.DefaultView[i]["Name"].ToString();
                    inRecCnt = 0;
                    IsContinue = false;
                    //迴圈跑是否持續進榜
                    for (int befDayCnt = 1; i < xls.Workbook.NumberOfSheets; befDayCnt++)
                    {
                        // befDayCnt 要往前推算幾天
                        //1.先取得工作表名稱
                        try
                        {
                            HSSFSheet befSheet = xls.Workbook.GetSheetAt(xls.Workbook.GetSheetIndex(dataDay) - befDayCnt);
                            IsContinue = false;
                            //2.跑工作表迴圈
                            //befSheet.LastRowNum應該為2~101
                            for (int befSheetRowCnt = 1; befSheetRowCnt <= befSheet.LastRowNum; befSheetRowCnt++)
                            {
                                //跌幅的IDNAME 在第9列
                                if (befSheet.GetRow(befSheetRowCnt).GetCell(9).StringCellValue == SIDName)
                                {
                                    IsContinue = true;
                                    inRecCnt++;
                                    break; //離開befSheet
                                }
                            }
                            if (IsContinue == false)
                                break; //離開計算持續進榜
                        }
                        catch { }
                    }
                    if (inRecCnt == 0)
                        sheet.CreateRow(x).CreateCell(16).SetCellValue("新進榜");
                    else //寫入次數並上色
                    {
                        HSSFCellStyle style1 = xls.Workbook.CreateCellStyle();
                        if (inRecCnt == 1)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;
                        else if (inRecCnt >= 2 && inRecCnt <= 5)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index;
                        else if (inRecCnt > 5)
                            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index;
                        style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
                        HSSFCell cell9 = sheet.CreateRow(x).CreateCell(9);
                        cell9.CellStyle = style1;
                        cell9.SetCellValue(dt2.DefaultView[i]["ID"].ToString() + " " + dt2.DefaultView[i]["Name"].ToString());

                        HSSFCell cell10 = sheet.CreateRow(x).CreateCell(10);
                        cell10.CellStyle = style1;
                        cell10.SetCellValue(dt2.DefaultView[i]["ClosingPrice"].ToString());

                        HSSFCell cell11 = sheet.CreateRow(x).CreateCell(11);
                        cell11.CellStyle = style1;
                        cell11.SetCellValue(dt2.DefaultView[i]["Fluctuation"].ToString());

                        HSSFCell cell12 = sheet.CreateRow(x).CreateCell(12);
                        cell12.CellStyle = style1;
                        cell12.SetCellValue(dt2.DefaultView[i]["FluctuationPerCent"].ToString());

                        HSSFCell cell13 = sheet.CreateRow(x).CreateCell(13);
                        cell13.CellStyle = style1;
                        cell13.SetCellValue(dt2.DefaultView[i]["Volume"].ToString());

                        HSSFCell cell14 = sheet.CreateRow(x).CreateCell(14);
                        cell14.CellStyle = style1;
                        cell14.SetCellValue(dt2.DefaultView[i]["D2Fluctuation"].ToString());

                        HSSFCell cell15 = sheet.CreateRow(x).CreateCell(15);
                        cell15.CellStyle = style1;
                        cell15.SetCellValue(dt2.DefaultView[i]["D2FluctuationPerCent"].ToString());

                        HSSFCell cell16 = sheet.CreateRow(x).CreateCell(16);
                        cell16.CellStyle = style1;
                        cell16.SetCellValue("持續進榜[" + inRecCnt.ToString() + "]");
                    }

                }
                catch
                {

                }
            }
            //讓目前的工作表選為預設
            xls.Workbook.ActiveSheetIndex = xls.Workbook.NumberOfSheets - 1;
            xls.Workbook.SetSelectedTab(xls.Workbook.NumberOfSheets - 1);
            sheet.IsSelected = true;
            totalSheetCnt = xls.Workbook.NumberOfSheets;
            // 寫入實體檔案
            xls.WriteToFile();
            
            //xls.ManageExcelFile( destFileName);          
        }

        /// <summary>
        /// 解析HTML new
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ds"></param>
        public void analysisHTML(string url, dds.ddtDataTable dt)
        {
            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            HtmlElementCollection tb = wb.Document.GetElementsByTagName("TABLE");
            HtmlElementCollection tr = tb[2].GetElementsByTagName("TR");
            int count = 0;
            string tmpDay = tr[0].InnerText.Substring(tr[0].InnerText.IndexOf("：") + 1);
            string[] daySplit = tmpDay.Split('/');
            if (daySplit.Length == 2)
            {
                if ((daySplit[0] == "12") && (DateTime.Today.Month == 1))
                    dataDay = string.Format("{0:0000}{1:00}{2:00}", DateTime.Today.Year - 1, daySplit[0], daySplit[1]);
                else
                    dataDay = string.Format("{0:0000}{1:00}{2:00}", DateTime.Today.Year, daySplit[0], daySplit[1]);
            }
            else
            {
                dataDay = string.Format("{0:0000}", DateTime.Today.Year);
            }
            // 系統日期與盤後資料日期不符
            // 假日會發生
            // 把系統日換成盤後資料日
            if (Program.DataDay.Equals(dataDay) == false)
                Program.DataDay = dataDay;

            foreach (HtmlElement trElement in tr)
            {
                HtmlElementCollection td = trElement.GetElementsByTagName("TD");



                if (count >= 2)
                {
                    dds.ddtRow record = dt.NewddtRow();
                    try
                    {
                        Regex rx = new Regex(@"[(']+(?<sid>\d+)+[' , ']+(?<sname>\w+)+[')];");
                        MatchCollection mc = Regex.Matches(td[1].InnerHtml, @"(?<sid>\d+)+[',']+(?<sname>\D+[^')])[']");
                        record.ID = mc[0].Groups["sid"].ToString();
                        record.Name = mc[0].Groups["sname"].ToString();
                    }
                    catch
                    {
                        record.ID = "";
                        record.Name = td[1].InnerText.TrimStart().Trim();
                    }

                    record.ClosingPrice = td[2].InnerText;
                    record.Fluctuation = td[3].InnerText;
                    record.FluctuationPerCent = td[4].InnerText;
                    record.Volume = Convert.ToInt32(td[5].InnerText.Replace(",", ""));
                    record.D2Fluctuation = td[6].InnerText;
                    record.D2FluctuationPerCent = td[7].InnerText;
                    dt.Rows.Add(record);
                }
                count++;
            }
            msg += tb.Count.ToString();
            wb.Dispose();
        }

    }
}
